<?php


class KroExcel_98 extends KroExcel
{

	public $workbook = null;
	public $formats = null;
	public $file = null;

	public function createBook($file_suff = '')
	{
		parent::createBook($file_suff);
		require_once(SEFLDIR.'xls/Worksheet.php');
		require_once(SEFLDIR.'xls/Workbook.php');	
		$this->workbook = new Workbook($this->filename);
		$this->formats = $this->_getFormats($this->workbook);
	}

	public function addExtension()
	{ 
		$this->filename .= '.xls';
		$this->filename_download .= '.xls';
	}

	public function createSheetBalance($sheet_name, $sheet_num, &$users)
	{
		global $VALUTA;

		$worksheet = &$this->workbook->add_worksheet($this->encode($sheet_name));
		$worksheet->write_string(0, 0, $this->encode('Статус участника'), $this->formats['head']);
		$worksheet->write_string(0, 1, $this->encode('Ф.И.О., (регион проживания), г.р.'), $this->formats['head']);
		$worksheet->write_string(0, 2, $this->encode('Контактные данные: email, skype логин и моб.телефон'), $this->formats['head']);
		$worksheet->write_string(0, 3, $this->encode('Дата'), $this->formats['head']);
		$worksheet->write_string(0, 4, $this->encode('cумма покупки/продажи'), $this->formats['head']);
		$worksheet->write_string(0, 5, $this->encode('Курс $МмМ покупки/продажи'), $this->formats['head']);
		$worksheet->write_string(0, 6, $this->encode('$МмМ'), $this->formats['head']);
		$worksheet->write_string(0, 7, $this->encode('Вид и место хранения средств'), $this->formats['head']);
		$worksheet->write_string(0, 8, $this->encode('Дополнительно'), $this->formats['head']);

		$row_num = 1;
		$user_num = 1;
		foreach($users as $k => $user)
		{
			if(!@$user['operations'] || !count($user['operations']))
			{
				continue;
			}
			
			$text_cell_0 = $user['id'] == Auth::$user_id ? '' : "№ ".$user_num;
			$worksheet->write_string($row_num, 0, $this->encode($text_cell_0), $this->formats['cell']);
			$worksheet->write_string($row_num, 1, '', $this->formats['cell']);
			$worksheet->write_string($row_num, 2, '', $this->formats['cell']);

			// Первая колонка
			$row_num_1 = $row_num + 1;
			$worksheet->write_string($row_num + 1, 0, $this->encode($user['status']), $this->formats['cell']);
			if($user['referer_email'])
			{
				$row_num_1 += 3;
				$worksheet->write_string($row_num + 2, 0, $this->encode('Реферер:'), $this->formats['cell']);
				$worksheet->write_string($row_num + 3, 0, $this->encode($user['referer_email']), $this->formats['cell']);
				$worksheet->write_string($row_num + 4, 0, $this->encode($user['referer_fio']), $this->formats['cell']);
			}

			// Вторая колонка
			$row_num_2 = $row_num + 2;
			$worksheet->write_string($row_num + 1, 1, $this->encode($user['fio']), $this->formats['cell']);
			$worksheet->write_string($row_num + 2, 1, $this->encode($user['otkuda']), $this->formats['cell']);

			// Третья колонка
			$row_num_3 = $row_num;
			if($user['email'])
			{
				$row_num_3++;
					$worksheet->write_string($row_num_3, 2, $this->encode($user['email']), $this->formats['cell']);
			}
			if($user['phone'])
			{
				$row_num_3++;
					$worksheet->write_string($row_num_3, 2, $this->encode("Телефон: ".$user['phone']), $this->formats['cell']);
			}
			if($user['skype'])
			{
				$row_num_3++;
					$worksheet->write_string($row_num_3, 2, $this->encode("Skype: ".$user['skype']), $this->formats['cell']);
			}
			if($user['icq'])
			{
				$row_num_3++;
					$worksheet->write_string($row_num_3, 2, $this->encode("ICQ: ".$user['icq']), $this->formats['cell']);
			}
			
			// Операции
			$row_num_oper = $row_num;
			foreach($user['operations'] as $oper)
			{
				$is_bonus = $oper['bonus'] && $oper['type'] != 'modify.buy' && $oper['type'] != 'modify.sell';
				$f_date = $is_bonus ? 'date_yellow' :'date';
				$worksheet->write_string($row_num_oper, 3, $this->encode($oper['day'].'.'.$oper['month'].'.'.$oper['year']), $this->formats[$f_date]);

				$f_summ = isBuy($oper) ? 'buy' : 'sell';
				$f_summ .= $is_bonus  ? "_yellow" : '';
				$worksheet->write_string($row_num_oper, 4, $this->encode($oper['summ'].' '.$VALUTA[$oper['valuta']]), $this->formats[$f_summ]);

				$f_kurs = isBuy($oper) ? 'kurs' : 'kurs_sell';
				$f_kurs .= $is_bonus  ? "_yellow" : '';
				$txt_kurs = $oper['type'] != 'week' && $oper['mavro'] > 0 ? $oper['valuta'].' '.$oper['kurs'].' '.$VALUTA[$oper['mavro_valuta']].' ('.$oper['percent'].'%)' : $oper['percent'].'%';
				$worksheet->write_string($row_num_oper, 5, $this->encode($txt_kurs), $this->formats[$f_kurs]);

				$f_mavro = isBuy($oper) ? 'buy' : 'sell';
				$f_mavro .= $is_bonus  ? "_yellow" : '';
				$txt_mavro = '';
				if($oper['type'] != 'week' && $oper['mavro'] > 0)
				{
					$txt_mavro = $oper['mavro'].' '.$oper['mavro_valuta'];
					if($oper['depo'])
					{
						$super = $oper['depo'] && substr($oper['percent'], -1) == 5 ? "super" : '';
						$txt_mavro .= '(Dep '.$oper['depo'].'m '.$super.')';
					}
				}
				$worksheet->write_string($row_num_oper, 6, $this->encode($txt_mavro), $this->formats[$f_mavro]);

				$f_hranen = $is_bonus ? 'hranen_yellow' : 'cell';
				$worksheet->write_string($row_num_oper, 7, $this->encode($oper['hranen']), $this->formats[$f_hranen]);

				$worksheet->write_string($row_num_oper, 8, $this->encode($oper['dop']), $this->formats['cell']);

				$row_num_oper++;
			}
			$row_num_oper--;

			$row_num = max($row_num_1, $row_num_2, $row_num_3, $row_num_oper);
			for($i = $row_num_1 + 1; $i <= $row_num; $i++) $worksheet->write_string($i, 0, '', $this->formats['cell']);
			for($i = $row_num_2 + 1; $i <= $row_num; $i++) $worksheet->write_string($i, 1, '', $this->formats['cell']);
			for($i = $row_num_3 + 1; $i < $row_num; $i++) $worksheet->write_string($i, 2, '', $this->formats['cell']);
			for($i = $row_num_oper + 1; $i <= $row_num; $i++)
			{
				$worksheet->write_string($i, 3, '', $this->formats['cell']);
				$worksheet->write_string($i, 4, '', $this->formats['cell']);
				$worksheet->write_string($i, 5, '', $this->formats['cell']);
				$worksheet->write_string($i, 6, '', $this->formats['cell']);
				$worksheet->write_string($i, 7, '', $this->formats['cell']);
				$worksheet->write_string($i, 8, '', $this->formats['cell']);
			}


			// Добавление последней строки
			$row_num++;
			$all_balance = 'Общий баланс $МмМ:';
			if(@$user['all_mavro'])
			{
				foreach($user['all_mavro'] as $valuta => $arr_valuta)
				{
					foreach($arr_valuta as $percent => $m)
					{
						$all_balance .= ' '.$m.' '.$valuta.' ('.$percent.'%);';
					}
				}
			}
			$worksheet->write_string($row_num, 0, '', $this->formats['last_cell']);
			$worksheet->write_string($row_num, 1, '', $this->formats['last_cell']);
			$worksheet->write_string($row_num, 2, '', $this->formats['last_cell']);
			$worksheet->write_string($row_num, 3, $this->encode($all_balance), $this->formats['head']);
			$worksheet->write_string($row_num, 4, '', $this->formats['head']);
			$worksheet->write_string($row_num, 5, '', $this->formats['head']);
			$worksheet->write_string($row_num, 6, '', $this->formats['head']);
			$worksheet->write_string($row_num, 7, '', $this->formats['last_cell']);
			$worksheet->write_string($row_num, 8, '', $this->formats['last_cell']);
			$worksheet->merge_cells($row_num, 3, $row_num, 6);
			$row_num++;
			if($user['id'] != Auth::$user_id)
			{
				$user_num++;
			}

		}

		$worksheet->set_column(0, 0, 30);
		$worksheet->set_column(1, 1, 40);
		$worksheet->set_column(2, 2, 50);
		$worksheet->set_column(3, 3, 15);
		$worksheet->set_column(4, 4, 15);
		$worksheet->set_column(5, 5, 20);
		$worksheet->set_column(6, 6, 25);
		$worksheet->set_column(7, 7, 65);
		$worksheet->set_column(8, 8, 40);
	}

	private function &_getFormats(&$workbook)
	{
		$format_head =& $workbook->add_format();
		$format_head->set_border(2);

		$format_cell =& $workbook->add_format();
		$format_cell->set_align('left');
		$format_cell->set_left(2);
		$format_cell->set_right(2);

		$format_date =& $workbook->add_format();
		$format_date->set_left(2);
		$format_date->set_right(2);

		$format_date_yellow =& $workbook->add_format();
		$format_date_yellow->set_fg_color('yellow');
		$format_date_yellow->set_left(2);
		$format_date_yellow->set_right(2);
		$format_date_yellow->set_pattern(1);

		$format_buy =& $workbook->add_format();
		$format_buy->set_color('green');
		$format_buy->set_left(2);
		$format_buy->set_right(2);

		$format_buy_yellow =& $workbook->add_format();
		$format_buy_yellow->set_color('green');
		$format_buy_yellow->set_fg_color('yellow');
		$format_buy_yellow->set_left(2);
		$format_buy_yellow->set_right(2);
		$format_buy_yellow->set_pattern(1);

		$format_sell =& $workbook->add_format();
		$format_sell->set_color('red');
		$format_sell->set_left(2);
		$format_sell->set_right(2);

		$format_sell_yellow =& $workbook->add_format();
		$format_sell_yellow->set_fg_color('yellow');
		$format_sell_yellow->set_color('red');
		$format_sell_yellow->set_left(2);
		$format_sell_yellow->set_right(2);
		$format_sell_yellow->set_pattern(1);

		$format_kurs =& $workbook->add_format();
		$format_kurs->set_color('purple');
		$format_kurs->set_left(2);
		$format_kurs->set_right(2);

		$format_kurs_sell =& $workbook->add_format();
		$format_kurs_sell->set_color('blue');
		$format_kurs_sell->set_left(2);
		$format_kurs_sell->set_right(2);

		$format_kurs_yellow =& $workbook->add_format();
		$format_kurs_yellow->set_color('purple');
		$format_kurs_yellow->set_fg_color('yellow');
		$format_kurs_yellow->set_left(2);
		$format_kurs_yellow->set_right(2);
		$format_kurs_yellow->set_pattern(1);

		$format_kurs_sell_yellow =& $workbook->add_format();
		$format_kurs_sell_yellow->set_color('blue');
		$format_kurs_sell_yellow->set_fg_color('yellow');
		$format_kurs_sell_yellow->set_left(2);
		$format_kurs_sell_yellow->set_right(2);
		$format_kurs_sell_yellow->set_pattern(1);

		$format_hranen_yellow =& $workbook->add_format();
		$format_hranen_yellow->set_fg_color('yellow');
		$format_hranen_yellow->set_align('left');
		$format_hranen_yellow->set_color('red');
		$format_hranen_yellow->set_left(2);
		$format_hranen_yellow->set_right(2);
		$format_hranen_yellow->set_pattern(1);

		$format_last_cell =& $workbook->add_format();
		$format_last_cell->set_bottom(2);
		$format_last_cell->set_left(2);
		$format_last_cell->set_right(2);

		$format_last_cell2 =& $workbook->add_format();
		$format_last_cell2->set_bottom(2);

		$out = array(
			'head' => &$format_head,
			'cell' => &$format_cell,
			'date' => &$format_date,
			'date_yellow' => &$format_date_yellow,
			'buy' => &$format_buy,
			'buy_yellow' => &$format_buy_yellow,
			'sell' => &$format_sell,
			'sell_yellow' => &$format_sell_yellow,
			'kurs' => &$format_kurs,
			'kurs_sell' => &$format_kurs_sell,
			'kurs_yellow' => &$format_kurs_yellow,
			'kurs_sell_yellow' => &$format_kurs_sell_yellow,
			'last_cell' => &$format_last_cell,
			'last_cell2' => &$format_last_cell2,
			'hranen_yellow' => &$format_hranen_yellow
		);
		return $out;
	}

	public function closeBook()
	{
		$this->workbook->close();
	}

	function encode($str)
	{
		try{
			$str2 = @iconv("utf-8", "windows-1251", $str);
		}
		catch(Exception $e)
		{
			$str2 = $str;
		}
		return $str2;
	}

}




